package com.kehutong.house.admin;

import java.io.File;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.kehutong.common.permission.DataPermissionQuery;
import com.kehutong.common.permission.PermissionNameDto;
import com.kehutong.house.service.BuildService;
import com.kehutong.house.service.HouseService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.coraframework.authz.HasPermission;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.logger.Logger;
import org.coraframework.logger.LoggerFactory;
import org.coraframework.mvc.MvcAction;
import org.coraframework.mvc.http.Bean;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.execute.entity.FindArray;
import org.coraframework.orm.jdbc.execute.entity.FindOne;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.Objects;

import com.kehutong.common.DoveClient;
import com.kehutong.common.ExcelUtil;
import com.kehutong.common.urlconfig.FileUrlConfig;
import com.kehutong.common.util.Token;
import com.kehutong.house.entity.Building;
import com.kehutong.house.entity.Community;
import com.kehutong.house.entity.House;

@MvcAction
@ReqMapping("/admin/basic/building")
public class BuildingController {
	
	@Inject
	private JdbcSession jdbcSession;
	@Inject
	private DoveClient doveClient;
	@Inject
	private FileUrlConfig fileUrlConfig;
	@Inject
	private HouseService houseService;
	@Inject
	private BuildService buildService;

	public final static String[] EXCEL_IMPORT_HEADER = {"项目/小区","楼栋","单元","别名","楼层数","总户数","所属部门","物业管家"};

	static final Logger logger = LoggerFactory.getLogger(BuildingController.class);

    @HasPermission("basic:building:view")
    @ReqMapping("/list")
    public Object list(JSONObject jsonObject) {
    	FindPage<Building> findPage = jdbcSession.findPage(Building.class);

		String name = jsonObject.getString("name");
		String unitName = jsonObject.getString("unitName");
		if(Objects.nonEmpty(name) || Objects.nonEmpty(unitName)){
			findPage.markBegin()
					.like("name", name).or()
					.like("unitName", unitName)
					.markEnd().and();
		}

		JSONArray employeeNos = jsonObject.getJSONArray("employeeNos");
		if(Objects.nonEmpty(employeeNos)){
			findPage.markBegin();
			for (Object item: employeeNos) {
				findPage.like("employeeNos",String.valueOf(item)).or();
			}
			findPage.markEnd().and();
		}

		findPage.eq(jsonObject, "communityNo")
    			.like(jsonObject, "communityName")
				.eq("deleted", false)
				.order("createTime", "desc")
				.page(jsonObject);

		try{
			PermissionNameDto dto = houseService.getBuildPermissionNameDto();
			houseService.setDataSql(findPage, dto, jsonObject, null);
			Page<Building> page = findPage.exe(false);

			Set<String> employeeIds = new HashSet<>();
			page.getList().forEach(item->{
				if(Objects.nonEmpty(item.getEmployeeNos())){
					employeeIds.addAll(item.getEmployeeNos());
				}
			});

			Map<String,JSONObject> employeeMap = getEmployeeMap(new ArrayList<>(employeeIds));
			page.getList().forEach(item->{
				List<String> employeeNames = new ArrayList<>();
				item.getEmployeeNos().forEach(id ->{
					JSONObject employee = employeeMap.get(id);
					if(Objects.nonNull(employee)){
						employeeNames.add(employee.getString("name"));
					}
				});
				item.setEmployeeNames(employeeNames);
			});

			return page;
		}catch (Exception e){
			logger.debug("page=========", e);
		}
		return new Page<>();
    }

	@HasPermission("basic:building:view")
	@ReqMapping("/getCascaders")
	public Object getCascaders(JSONObject jsonObject) {
		FindArray<Community> find = jdbcSession.findArray(Community.class)
				.eq("deleted", false);

		PermissionNameDto dto = houseService.getCommunityPermissionNameDto();

		DataPermissionQuery.setAllDataSql(find, dto, null);

		List<Community> communitys = find.exe(false);

		Map<String, List<JSONObject>> buildMap = getBuildingMap();

		List<JSONObject> array = new ArrayList<>();
		communitys.forEach(item->{
			array.add(community2JSONObject(item, buildMap));
		});

    	return array;
	}

	public Map<String, List<JSONObject>> getBuildingMap() {
		FindArray<Building> find = jdbcSession.findArray(Building.class)
				.eq("deleted", false);

		PermissionNameDto dto = houseService.getBuildPermissionNameDto();

		DataPermissionQuery.setAllDataSql(find, dto, null);

		List<Building> list = find.exe(false);

		Map<String, List<JSONObject>> result = new HashMap<>();
		list.forEach(item->{
			List<JSONObject> builds = result.get(item.getCommunityNo());
			if(Objects.isNull(builds)){
				builds = new ArrayList<>();
			}
			builds.add(build2JSONObject(item));
			result.put(item.getCommunityNo(), builds);
		});

		return result;
	}

	private JSONObject build2JSONObject(Building build) {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("value", build.getId());
		jsonObject.put("label", Objects.nonEmpty(build.getUnitName()) ? build.getName() + "-" + build.getUnitName() : build.getName());
		return jsonObject;
	}

	private JSONObject community2JSONObject(Community community, Map<String, List<JSONObject>> buildMap) {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("value", community.getId());
		jsonObject.put("label", community.getName());
		jsonObject.put("children", buildMap.get(community.getId()));
		return jsonObject;
	}

    @HasPermission("basic:building:view")
    @ReqMapping("/get")
    public Object get(Building item) {
    	return item;
    }

	@ReqMapping("/setStatus")
	public Object setStatus(Building entity) {
		entity.setEnable(!entity.isEnable());
		jdbcSession.updateById(entity);

		return Result.success();
	}
    
    @Bean(newInstance=true, copy=true)
    @HasPermission("basic:building:edit")
    @ReqMapping("/save")
    public Object save(Token token, Building building) throws Exception {
		return buildService.save(token, building);
    }

    @Bean(copy=true, isIgnoreNull = false)
    @HasPermission("basic:building:edit")
    @ReqMapping("/update")
    public Object update(Building building) throws Exception {
    	return buildService.update(building);
    }

    @HasPermission("basic:building:edit")
    @ReqMapping("/delete")
	public Object delete(Building item) {
		return buildService.delete(item);
	}

	@HasPermission("basic:community:edit")
	@ReqMapping("/deleteAll")
	public Object deleteAll(JSONObject jsonObject){
		List<Building> list = jdbcSession.findArray(Building.class)
				.in("id", jsonObject.getJSONArray("ids"))
				.eq("deleted", false)
				.exe();

		List<String> errMsgs = new ArrayList<>();
		list.forEach(item ->{
			List<House> houses = jdbcSession.findArray(House.class)
					.eq("communityNo", item.getCommunityNo())
					.eq("buildNo", item.getId())
					.eq("deleted", false)
					.exe();

			if(Objects.nonEmpty(houses)){
				if(Objects.nonEmpty(item.getUnitName())){
					errMsgs.add("[" + item.getName() + "-" + item.getUnitName() + "]需先删除该楼栋下的房屋，再删除该楼栋！");
				}else{
					errMsgs.add("[" + item.getName() + "]需先删除该楼栋下的房屋，再删除该楼栋！");
				}
			}else{
				item.setDeleted(true);
			}
		});

		if (!errMsgs.isEmpty()) {
			return Result.error(0, "删除失败", errMsgs);
		}

		jdbcSession.updateById(list);

		return Result.success();
	}

	@ReqMapping("/checkFile")
	public Object checkFile(JSONObject jsonObject) throws Exception {
		InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
		Workbook wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);

		// 3行表头 开始读
		Row firstRow = sheet.getRow(sheet.getFirstRowNum() + 2);
		List<String> errMsgs = new ArrayList<>();

		for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
			firstRow.getCell(i).setCellType(CellType.STRING);
			String s = firstRow.getCell(i).getStringCellValue();
			if (i < EXCEL_IMPORT_HEADER.length) {
				if (!EXCEL_IMPORT_HEADER[i].equals(s)) {
					errMsgs.add("请确认【" + EXCEL_IMPORT_HEADER[i] + "】为第" + (i + 1) + "列");
				}
			}
		}

		JSONObject errorJson = new JSONObject();
		// 表头校验结果
		if (!errMsgs.isEmpty()) {
			errorJson.put("normalCount", 0);
			errorJson.put("errCount", 0);
			errorJson.put("successCount", 0);
			errorJson.put("errMsgs", errMsgs);
			return Result.error(0, "数据解析异常", errorJson);
		}

		Map<Integer,Integer> errCount = new HashMap<>();
		Integer size = 0;

		Map<String, JSONObject> departmentMap = getDepartmentMap();
		// 第4行开始遍历获取数据
		for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
			Row row = sheet.getRow(rIndex);

			if(Objects.isNull(row)){
				continue;
			}

			List<String> cellValues = new ArrayList<>();
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				Cell cell = row.getCell(i);
				if (i < EXCEL_IMPORT_HEADER.length && Objects.nonNull(cell)) {
					cell.setCellType(CellType.STRING);
					String value = cell.getStringCellValue();
					if(Objects.nonEmpty(value)){
						cellValues.add(value);
					}
				}
			}
			if(Objects.isEmpty(cellValues)){
				continue;
			}

			// 校验数据 写入json数据
			checkExcelData(row, (rIndex + 1), errMsgs, departmentMap, errCount);

			size++;
		}

		errorJson.put("normalCount", size);
		if(size > 3000){
			errMsgs = new ArrayList<>();
			errMsgs.add("最多支持导入3000条数据");
			errorJson.put("errCount", 0);
			errorJson.put("successCount", 0);
			errorJson.put("errMsgs", errMsgs);
			return Result.error(0, "数据解析异常", errorJson);
		}

		// 数据读取结果
		if (!errMsgs.isEmpty()) {

			int errSum = 0;
			for (int val: errCount.keySet()) {
				errSum++;
			}

			errorJson.put("errMsgs", errMsgs);
			errorJson.put("errCount", errSum);
			errorJson.put("successCount", size - errSum);

			return Result.error(0, "数据解析异常", errorJson);
		}

		errorJson.put("errMsgs", errMsgs);
		errorJson.put("errCount", 0);
		errorJson.put("successCount", size);
		return Result.success("成功", errorJson);
	}

	@ReqMapping("/readFile")
	public Object readFile(Token token, JSONObject jsonObject) throws Exception {
		InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
		Workbook wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);

		List<JSONObject> jsons = new ArrayList<>();

		// 第4行开始遍历获取数据
		for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
			Row row = sheet.getRow(rIndex);

			if(Objects.isNull(row)){
				continue;
			}

			List<String> cellValues = new ArrayList<>();
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				Cell cell = row.getCell(i);
				if (i < EXCEL_IMPORT_HEADER.length && Objects.nonNull(cell)) {
					cell.setCellType(CellType.STRING);
					String value =  cell.getStringCellValue();
					if(Objects.nonEmpty(value)){
						cellValues.add(value);
					}
				}
			}
			if(Objects.isEmpty(cellValues)){
				continue;
			}

			JSONObject json = new JSONObject();
			// 校验数据 写入json数据
			checkExcelData(row, json);

			jsons.add(json);
		}

		List<String> childDepartments = buildService.getChildDepartments(token.getDepartments());

		jsons.forEach(json->{
			Building building = jdbcSession.findOne(Building.class)
					.eq("deleted", false)
					.eq(json, "communityNo","name","unitName")
					.exe();
			if(Objects.nonNull(building)){
				setExcelData(building, json);
				jdbcSession.updateById(building);
			}else{
				building = Pool.newInstance(Building.class);
				setExcelData(building, json);

				building.setCreate_by(token.getUsername());
				building.setCreateUserId(token.getUuid());
				building.setCreateDepartment(token.getDepartments());
				building.setCreateChildDepartment(childDepartments);

				jdbcSession.insert(building);
			}
		});

		return Result.success();
	}

	private void setExcelData(Building item, JSONObject json){
		item.setCommunityNo(json.getString("communityNo"));
		item.setCommunityName(json.getString("communityName"));
		item.setName(json.getString("name"));
		item.setUnitName(json.getString("unitName"));
		item.setViewName(json.getString("viewName"));
		item.setFloorName(json.getInteger("floorName"));
		item.setFamilyNum(json.getInteger("familyNum"));
		String employeeNos = json.getString("employeeNos");

		Community com = Pool.get(Community.class, item.getCommunityNo());
		item.setDepartmentNo(com.getParent_id());
		item.setDepartmentNos(com.getParent_ids());

		if(Objects.nonEmpty(employeeNos)){
			item.setEmployeeNos(Arrays.asList(employeeNos.split(",")));
		}else{
			item.setEmployeeNos(null);
		}
	}

	private void checkExcelData(Row row, int index, List<String> errMsgs, Map<String, JSONObject> departmentMap, Map<Integer,Integer> errorCount) throws Exception {
		Community community = null;
		String communityName = ExcelUtil.getCellValue(row.getCell(0));
		if (Objects.isEmpty(communityName)) {
			errorCount.put(index, 1);
			errMsgs.add("第" + index + "行：【项目/小区】不能为空");
		}else{
			community = jdbcSession.findOne(Community.class)
					.eq("deleted", false)
					.eq("name", communityName)
					.exe();
			if(Objects.isNull(community)){
				errorCount.put(index, 1);
				errMsgs.add("第"+ index +"行,未获取到当前【小区信息】,请检查");
			}
		}

		String buildName = ExcelUtil.getCellValue(row.getCell(1));
		if(Objects.isEmpty(buildName)){
			errorCount.put(index, 1);
			errMsgs.add("第" + index + "行：【楼栋】不能为空");
		}

        try {
            ExcelUtil.getBigDecimalFromCell(row, 4);
        }catch (Exception e){
            errorCount.put(index, 1);
            errMsgs.add("第" + index + "行：【楼层数】校验出现异常请检查");
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row, 5);
		}catch (Exception e){
            errorCount.put(index, 1);
            errMsgs.add("第" + index + "行：【总户数】校验出现异常请检查");
        }

        String departmentName = ExcelUtil.getCellValue(row.getCell(6));
		if(Objects.nonEmpty(departmentName) && Objects.nonNull(community)){
			String[] departmentNames = departmentName.split("-");
			String departName = departmentNames[0];

			JSONObject department = departmentMap.get(community.getParent_id());
			if(Objects.nonNull(department) && !departName.equals(department.getString("name"))){
				errorCount.put(index, 1);
				errMsgs.add("第" + index + "行：【所属部门】与小区的所属部门不一致");
			}
			if(departmentNames.length > 1){
				String commName = departmentNames[1];
				if(!commName.equals(communityName)){
					errorCount.put(index, 1);
					errMsgs.add("第" + index + "行：【所属部门】与小区的名称不一致");
				}
			}
		}

		String employeeName = ExcelUtil.getCellValue(row.getCell(7));
		if(Objects.nonNull(community) && Objects.nonEmpty(employeeName)){
			if(community.getEmployee_ids().size() == 0){
				errorCount.put(index, 1);
				errMsgs.add("第" + index + "行：【当前小区】下没有物业管家");
			}else{
				List<String> employeeNames = Arrays.asList(employeeName.split(","));
				List<String> employeeIds = new ArrayList<>();
				for (String empName: employeeNames) {
					JSONArray employees = getEmployee(community.getEmployee_ids(), empName);
					if(employees.size() == 0){
						errorCount.put(index, 1);
						errMsgs.add("第" + index + "行：未在小区管家中找到【"+empName+"】员工");
					}else{
						JSONObject employee = (JSONObject) employees.get(0);
						employeeIds.add(employee.getString("id"));
					}
				}
			}
		}
	}

	private void checkExcelData(Row row, JSONObject json) throws Exception {
    	Community community = null;
		String communityName = ExcelUtil.getCellValue(row.getCell(0));
		if (Objects.nonEmpty(communityName)) {
			community = jdbcSession.findOne(Community.class)
					.eq("deleted", false)
					.eq("name", communityName)
					.exe();
			if(Objects.nonNull(community)){
				json.put("communityName", communityName);
				json.put("communityNo", community.getId());
			}
		}

        json.put("name", ExcelUtil.getCellValue(row.getCell(1)));

        json.put("unitName", ExcelUtil.getCellValue(row.getCell(2)));

		json.put("viewName", ExcelUtil.getCellValue(row.getCell(3)));

		try {
		    BigDecimal floorName = ExcelUtil.getBigDecimalFromCell(row, 4);
		    if (Objects.nonNull(floorName)) {
		        json.put("floorName", floorName.intValue());
		    }
		}catch (Exception e){
		}

        try {
            BigDecimal familyNum = ExcelUtil.getBigDecimalFromCell(row, 5);
            if (Objects.nonNull(familyNum)) {
                json.put("familyNum", familyNum.intValue());
            }
        }catch (Exception e){
        }

		String employeeName = ExcelUtil.getCellValue(row.getCell(7));
		if(Objects.nonNull(community) && Objects.nonEmpty(employeeName)){
			if(community.getEmployee_ids().size() > 0){
				List<String> employeeNames = Arrays.asList(employeeName.split(","));
				List<String> employeeIds = new ArrayList<>();
				for (String empName: employeeNames) {
					JSONArray employees = getEmployee(community.getEmployee_ids(), empName);
					if(employees.size() > 0) {
						JSONObject employee = (JSONObject) employees.get(0);
						employeeIds.add(employee.getString("id"));
					}
				}
				json.put("employeeNos", String.join(",", employeeIds));
			}
		}
	}

	public JSONArray getEmployee(List<String> ids, String name) throws Exception {
		JSONObject result = doveClient.post("/basic/employee/service/list", http->{
			JSONObject body = new JSONObject();
			body.put("id", ids);
			body.put("name", name);

			body.put("pageNo", 1);
			body.put("pageSize", 1000000);

			http.setBody(body.toString());
		});

		JSONArray data = result.getJSONObject("data").getJSONArray("list");

		if (Objects.isNull(data)) {
			return null;
		}

		return data;
	}

	@HasPermission("basic:building:view")
	@ReqMapping("/export")
	public Object export(JSONObject jsonObject) throws Exception {

		File tmpFile = null;
		Boolean isModel = jsonObject.getBoolean("isModel");

		if(isModel){
			Workbook wb = generateWorkBook();

			tmpFile = ExcelUtil.writeXlsFile(wb, "楼栋单元导入模板", ".xlsx");
		}else {
			jsonObject.put("pageNo",1);
			jsonObject.put("pageSize",Short.MAX_VALUE);

			FindPage<Building> findPage = jdbcSession.findPage(Building.class);
			String name = jsonObject.getString("name");
			String unitName = jsonObject.getString("unitName");
			if(Objects.nonEmpty(name) || Objects.nonEmpty(unitName)){
				findPage.markBegin()
						.like("name", name).or()
						.like("unitName", unitName)
						.markEnd().and();
			}


			JSONArray employeeNos = jsonObject.getJSONArray("employeeNos");
			if(Objects.nonEmpty(employeeNos)){
				findPage.markBegin();
				for (Object item: employeeNos) {
					findPage.like("employeeNos",String.valueOf(item)).or();
				}
				findPage.markEnd().and();
			}

			findPage.eq(jsonObject, "communityNo")
					.like(jsonObject, "communityName")
					.eq("deleted", false)
					.page(jsonObject);

			PermissionNameDto dto = houseService.getBuildPermissionNameDto();
			houseService.setDataSql(findPage, dto, jsonObject, null);
			Page<Building> page = findPage.exe(false);

			Set<String> employeeIds = new HashSet<>();
			page.getList().forEach(item->{
				if(Objects.nonEmpty(item.getEmployeeNos())){
					employeeIds.addAll(item.getEmployeeNos());
				}
			});
			Map<String,JSONObject> employeeMap = getEmployeeMap(new ArrayList<>(employeeIds));
			page.getList().forEach(item->{
				List<String> employeeNames = new ArrayList<>();
				item.getEmployeeNos().forEach(id ->{
					JSONObject employee = employeeMap.get(id);
					if(Objects.nonNull(employee)){
						employeeNames.add(employee.getString("name"));
					}
				});
				item.setEmployeeNames(employeeNames);
			});
			Workbook wb = generateWorkBook(page.getList());
			tmpFile = ExcelUtil.writeXlsFile(wb, "楼栋单元信息", ".xlsx");
		}

		return Result.download(tmpFile);
	}

	public XSSFWorkbook generateWorkBook() throws Exception {
		// 生成模板文件
		XSSFWorkbook wb = new XSSFWorkbook();

		// 设置头信息
		Sheet sheet = setSheetCellStyle(wb);

		int cellNum = 0;

		Row row = sheet.createRow(3);
		row.createCell(cellNum++).setCellValue("七星商业广场");
		row.createCell(cellNum++).setCellValue("1栋");
        row.createCell(cellNum++).setCellValue("1单元");
		row.createCell(cellNum++).setCellValue("1栋A单元");
		row.createCell(cellNum++).setCellValue("33层");
		row.createCell(cellNum++).setCellValue("120户");
		row.createCell(cellNum++).setCellValue("");

		for (int i = 0; i < cellNum; i++) {
			this.autoSizeColumn(sheet, i);
		}

		return wb;
	}

	public XSSFWorkbook generateWorkBook(List<Building> list) throws Exception {
		// 生成模板文件
		XSSFWorkbook wb = new XSSFWorkbook();

		// 设置头信息
		Sheet sheet = setSheetCellStyle(wb);

		Set<String> employeeIds = new HashSet<>();
		list.forEach(item->{
			if(Objects.nonEmpty(item.getEmployeeNos())){
				employeeIds.addAll(item.getEmployeeNos());
			}
		});

		Map<String,JSONObject> departmentMap = getDepartmentMap();
		Map<String,JSONObject> employeeMap = getEmployeeMap(new ArrayList<>(employeeIds));

		int cellNum = 0;
		for (int i = 0; i < list.size(); i++) {
			cellNum = 0;
			Building build = list.get(i);
			Row row = sheet.createRow(i + 3);
			row.createCell(cellNum++).setCellValue(build.getCommunityName());
			row.createCell(cellNum++).setCellValue(build.getName());
            row.createCell(cellNum++).setCellValue(build.getUnitName());
			row.createCell(cellNum++).setCellValue(build.getViewName());
			if(Objects.nonNull(build.getFloorName())){
                row.createCell(cellNum++).setCellValue(build.getFloorName());
            }else{
                row.createCell(cellNum++).setCellValue("");
            }
            if(Objects.nonNull(build.getFamilyNum())){
                row.createCell(cellNum++).setCellValue(build.getFamilyNum());
            }else{
                row.createCell(cellNum++).setCellValue("");
            }

			if(Objects.nonEmpty(build.getDepartmentNo())){
			   JSONObject department = departmentMap.get(build.getDepartmentNo());
			   if(Objects.nonEmpty(department)){
				   row.createCell(cellNum++).setCellValue(department.getString("name") + "-" + build.getCommunityName());
			   }
			}else{
				row.createCell(cellNum++).setCellValue("");
			}

			List<String> empNames = new ArrayList<>();
			build.getEmployeeNos().forEach(id->{
				JSONObject employee = employeeMap.get(id);
				if(Objects.nonEmpty(employee)){
					empNames.add(employee.getString("name"));
				}
			});
			row.createCell(cellNum++).setCellValue(String.join(",", empNames));
		}

		for (int i = 0; i < cellNum; i++) {
			this.autoSizeColumn(sheet, i);
		}
		return wb;
	}

	/**
	 * 自动列宽 为了初始展开时须知内容展示信息比较全故设置最小宽度
 	 */
	public void autoSizeColumn(Sheet sheet, int i) {
		if (sheet == null) {
			return;
		}

		// 设置列宽
		int mixWidth = new Double((20+0.71)*256).intValue();

		// 调整每一列宽度
		sheet.autoSizeColumn(i);
		// 解决自动设置列宽中文失效的问题
		int width = sheet.getColumnWidth(i) * 17 / 10;

		if(width < mixWidth){
			width = mixWidth;
		}
		// 避免宽度过长导致抛异常
		if (width > 255 * 256) {
			width = 255 * 10;
		}
		sheet.setColumnWidth(i, width);
	}

	private Sheet setSheetCellStyle(XSSFWorkbook wb){
		//设置字体颜色+文字水平居中
		CellStyle fontColorCellStyle = wb.createCellStyle();
		XSSFFont font = wb.createFont();
		XSSFColor color = new XSSFColor(new java.awt.Color(255,0,0));
		font.setColor(color);
		fontColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
		fontColorCellStyle.setFont(font);

		// 允许文字换行
		XSSFFont fontStyle = wb.createFont(); // 字体样式
//		fontStyle.setBold(true);// 加粗
		fontStyle.setFontName("宋体");
		fontStyle.setFontHeightInPoints((short) 16); // 大小
		CellStyle noticeCellStyle = wb.createCellStyle();
		noticeCellStyle.setWrapText(true);
		noticeCellStyle.setFont(fontStyle);

		// 文字水平居中
		CellStyle fontCellStyle = wb.createCellStyle();
		fontCellStyle.setAlignment(HorizontalAlignment.CENTER);

		Sheet sheet = wb.createSheet();

		// 设置第1行 合并17格
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
		// 设置第2行 合并格
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 7));

		//第1行 须知内容
		Row noticeRow = sheet.createRow(0);
		Cell noticeRowCell = noticeRow.createCell(0);
		noticeRowCell.setCellStyle(noticeCellStyle);
		StringBuffer notice = new StringBuffer();
		notice.append("填写须知：").append("\n")
				.append(" <1>不能在本excel表中对项目信息进行增加、删除、修改，若项目不足，请进入“企业管理后台-应用管理-资产管理”进行添加，完成后重新下载新模板；").append("\n")
				.append(" <2>红色字段为必填字段，黑色字段为选填字段；").append("\n")
				.append(" <3>所属部门：上下级部门间用‘-’隔开，且从最上级部门开始，例如“万科物业有限公司-深圳事业部-七星商业广场”；").append("\n")
				.append(" <4>项目/小区：请先到资产管理-项目列表先添加好或先通过excel导入好，再到本excel里填入；").append("\n");

		noticeRowCell.setCellValue(notice.toString());
		//自动设置行高
		int colLength = noticeRowCell.getStringCellValue().getBytes().length * 6;
		noticeRow.setHeight((short) (colLength));

		//第2行 员工信息：
		Row firstRow = sheet.createRow(1);
		Cell firstRowCell = firstRow.createCell(0);
		firstRowCell.setCellValue("楼栋信息");
		firstRowCell.setCellStyle(fontCellStyle);
		firstRowCell = firstRow.createCell(6);
		firstRowCell.setCellValue("物业管家");
		firstRowCell.setCellStyle(fontCellStyle);

		//第3行 表头
		Row headerRow = sheet.createRow(2);
		for (int i = 0; i < EXCEL_IMPORT_HEADER.length; i++) {
			Cell cell = headerRow.createCell(i);
			if(i <= 1){
                cell.setCellStyle(fontColorCellStyle);
			}else{
                cell.setCellStyle(fontCellStyle);
			}
			cell.setCellValue(EXCEL_IMPORT_HEADER[i]);
		}

		return sheet;
	}

	public Map<String,JSONObject> getEmployeeMap(List<String> employeeIds) throws Exception {
		JSONObject result = doveClient.post("/basic/employee/service/list", http->{
			JSONObject body = new JSONObject();
			body.put("id", employeeIds);
			body.put("pageNo", 1);
			body.put("pageSize", 1000000);
			http.setBody(body.toString());
		});

		JSONArray data = result.getJSONObject("data").getJSONArray("list");

		if (Objects.isNull(data)) {
			return null;
		}

		Map<String,JSONObject> employeeMap = new HashMap<>();
		data.forEach(item->{
			JSONObject obj = (JSONObject) item;
			employeeMap.put(obj.getString("id"), obj);
		});

		return employeeMap;
	}

	public Map<String,JSONObject> getDepartmentMap() throws Exception {
		JSONObject result = doveClient.post("/basic/department/service/list", http->{});

		JSONArray data = result.getJSONArray("data");

		if (Objects.isNull(result) || Objects.isNull(data)) {
			return new HashMap<>();
		}

		Map<String,JSONObject> departmentMap = new HashMap<>();
		data.forEach(item->{
			JSONObject obj = (JSONObject) item;
			departmentMap.put(obj.getString("id"), obj);
		});

		return departmentMap;
	}
}
